package com.nextStep.controller;

import com.fasterxml.jackson.databind.ObjectMapper;
import com.nextStep.entity.DbConfig;
import com.nextStep.entity.SqlDescComm;
import com.nextStep.entity.Users;
import com.nextStep.service.DbConfigService;
import com.nextStep.service.DbExecuteService;
import com.sun.xml.internal.ws.resources.HttpserverMessages;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Controller;
import org.springframework.web.HttpRequestHandler;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import javax.servlet.http.HttpServletRequest;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.SQLException;
import java.util.*;

@Controller
@RequestMapping("/db")
public class DbConfigController {

    @Autowired
    @Qualifier("dbConfigService")
    private DbConfigService dbConfigService;  //数据库配置

    @Autowired
    @Qualifier("dbExecute")
    private DbExecuteService dbExecuteService;  //数据库执行
    //跳转到主页
    @RequestMapping("/index")
    public String index(HttpServletRequest request){
        Users user = (Users) request.getSession().getAttribute("LOGIN_DATA");
        List<DbConfig> listDbConfig = dbConfigService.findConfig(user.getId());
        Map dataBase = new HashMap();  //key:数据库名  value:[ArrayList] 数据库表名

        for (int i = 0; i < listDbConfig.size(); i++) {   //获得所有数据库名及表名
            List<String> listTable = dbExecuteService.showTables(listDbConfig.get(i));  //获得当前数据库所有表名
            dataBase.put(listDbConfig.get(i).getDbname(),listTable);   //将当前数据库所有表名添加  key：数据库名
        }
        request.getSession().setAttribute("DATABASE_DATA",dataBase);
        return "index";
    }


    //向新注册用户插入默认数据
    @RequestMapping("/newUserAddDefaultData")
    public @ResponseBody boolean newUserAddDefaultData(String userid){
        DbConfig dbConfig = new DbConfig();
        dbConfig.setUserid(userid);
        dbConfig.setDburl("jdbc:mysql://47.98.231.255/mysqlExecuteTest");
        dbConfig.setDbusername("mysqlExecuteTestUser");
        dbConfig.setDbpassword("mysqlExecuteTestPassword123.");
        dbConfig.setDbname("mysqlExecuteTest");
        dbConfig.setDbid(UUID.randomUUID().toString().replaceAll("-",""));
        int status = dbConfigService.addConfig(dbConfig);
        if(status == 1){
            return true;
        }
        return false;
    }

    //显示当前数据库的所有表
    @RequestMapping("/showTable")
    @ResponseBody
    public List<String> showTable(HttpServletRequest request,String dbName){
        Map dataBase = (Map) request.getSession().getAttribute("DATABASE_DATA");
        List<String> listTable = (List<String>) dataBase.get(dbName);
        return  listTable;
    }

    //显示当前表所有的列名
    @RequestMapping("/selectColumns")
    public @ResponseBody List<String> selectColumns (HttpServletRequest request,String dbName,String tbName) {
        Users user = (Users) request.getSession().getAttribute("LOGIN_DATA");   //获取当前用户id
        List<DbConfig> listDbConfig = dbConfigService.findConfig(user.getId()); //获取当前用户的数据库配置
        List listColumns = null;
        for (int i = 0; i < listDbConfig.size(); i++) {
            if (dbName.equals(listDbConfig.get(i).getDbname())) {   //找到要请求的数据库
                listColumns = dbExecuteService.selectColumns(listDbConfig.get(i), tbName);
                break;
            }
        }
        return listColumns;
    }

    //查询表所有内容,包括表列名
    @RequestMapping("/selectTable")
    public @ResponseBody List selectTable(HttpServletRequest request,String dbName,String tbName,Integer pageNum,Integer pageSize){
        Users user = (Users)request.getSession().getAttribute("LOGIN_DATA");  //获取当前登录对象
        List<DbConfig> listConfig = dbConfigService.findConfig(user.getId());  //查找当前用户的所有配置
        List listTable = null;

        for (int i = 0; i < listConfig.size(); i++) {
            if(dbName.equals(listConfig.get(i).getDbname())){    //遍历所有配置，找到需要的配置
                listTable = dbExecuteService.selectTable(listConfig.get(i),tbName,pageNum,pageSize);    //查询表内容
                break;
            }
        }

        return listTable;
    }

    //根据条件查询表内容,包括表列名
    @RequestMapping("/condTable")
    public @ResponseBody List condTable(HttpServletRequest request, String dbName, String tbName,  String selectColumns, String dataSelectInput){
        Users user = (Users)request.getSession().getAttribute("LOGIN_DATA");  //获取当前登录对象
        List<DbConfig> listConfig = dbConfigService.findConfig(user.getId());  //查找当前用户的所有配置
        List listTable = null;

        for (int i = 0; i < listConfig.size(); i++) {
            if(dbName.equals(listConfig.get(i).getDbname())){    //遍历所有配置，找到需要的配置
                listTable = dbExecuteService.condTable(listConfig.get(i),tbName,selectColumns,dataSelectInput);    //查询表内容
                break;
            }
        }

        return listTable;
    }

    //显示数据的分页页面总数
    @RequestMapping("/selectLimitSumNum")
    public @ResponseBody Integer selectLimitSumNum(HttpServletRequest request,String dbName,String tbName,Integer pageSize) {
        Users user = (Users)request.getSession().getAttribute("LOGIN_DATA");  //获取当前登录对象
        List<DbConfig> listConfig = dbConfigService.findConfig(user.getId());  //查找当前用户的所有配置
        Integer pageLimit = null;
        for (int i = 0; i < listConfig.size(); i++) {
            if(dbName.equals(listConfig.get(i).getDbname())){    //遍历所有配置，找到需要的配置
                pageLimit = dbExecuteService.selectLimitSumNum(listConfig.get(i),tbName,pageSize);    //查询表内容
                break;
            }
        }
        return pageLimit;
    }

    //删除数据库配置
    @RequestMapping("/dbconfigDel")
    public @ResponseBody int dbconfigDel(String dbid){
        int status = dbConfigService.delConfig(dbid);
        return status;
    }

    //删除请求
    @RequestMapping("/deleteData")
    public @ResponseBody int deleteData(HttpServletRequest request,String dbName,String tbName,String idValue){
        Users user = (Users)request.getSession().getAttribute("LOGIN_DATA");  //获取当前登录对象
        List<DbConfig> listConfig = dbConfigService.findConfig(user.getId());  //查找当前用户的所有配置
        int status  = 0;
        for (int i = 0; i < listConfig.size(); i++) {
            if(dbName.equals(listConfig.get(i).getDbname())){    //遍历所有配置，找到需要的配置
                status = dbExecuteService.deleteData(listConfig.get(i),tbName,idValue);    //查询表内容
                break;
            }
        }
        return status;
    }
    //添加记录 构造添加页面
    @RequestMapping("/addRowPage")
    public String addRowPage(HttpServletRequest request,String dbName,String tbName){
        Users user = (Users)request.getSession().getAttribute("LOGIN_DATA");  //获取当前登录对象
        List<DbConfig> listConfig = dbConfigService.findConfig(user.getId());  //查找当前用户的所有配置
        List list = null;
        for (int i = 0; i < listConfig.size(); i++) {
            if(dbName.equals(listConfig.get(i).getDbname())){    //遍历所有配置，找到需要的配置
                list= dbExecuteService.selectColumns(listConfig.get(i),tbName);    //查询表结构 列名
                break;
            }
        }
        request.getSession().setAttribute("adRowDb",dbName);
        request.getSession().setAttribute("adRowTb",tbName);
        request.getSession().setAttribute("adRowDa",list);
        return "addRow";
    }

    //添加  添加到数据库
    @RequestMapping("/addRow")
    public @ResponseBody Integer addRow(HttpServletRequest request,String dbName,String tbName,String[] arrId,String[] arrValue){
        Users user = (Users)request.getSession().getAttribute("LOGIN_DATA");  //获取当前登录对象
        List<DbConfig> listConfig = dbConfigService.findConfig(user.getId());  //查找当前用户的所有配置
        Integer status = 0;
        for (int i = 0; i < listConfig.size(); i++) {
            if(dbName.equals(listConfig.get(i).getDbname())){    //遍历所有配置，找到需要的配置
                status= dbExecuteService.addRow(listConfig.get(i),tbName,arrId,arrValue);    //查询表结构 列名
                break;
            }
        }
        return status;
    }

    //修改   构造修改页面
    @RequestMapping("/updateRow")
    public  String updateRow(HttpServletRequest request,String dbName,String tbName,String rowIdValue){
        Users user = (Users)request.getSession().getAttribute("LOGIN_DATA");  //获取当前登录对象
        List<DbConfig> listConfig = dbConfigService.findConfig(user.getId());  //查找当前用户的所有配置
        List list = null;
        for (int i = 0; i < listConfig.size(); i++) {
            if(dbName.equals(listConfig.get(i).getDbname())){    //遍历所有配置，找到需要的配置
                list= dbExecuteService.updateRow(listConfig.get(i),tbName,rowIdValue);    //查询表内容
                break;
            }
        }
        request.getSession().setAttribute("upRowDb",dbName);
        request.getSession().setAttribute("upRowTb",tbName);
        request.getSession().setAttribute("upRowDa",list);
        return "updateRow";
    }



    //修改  修改数据库数据
    @RequestMapping("/updateRowData")
    public @ResponseBody int updateRowData(HttpServletRequest request,String dbName,String tbName,String[] arrId,String[] arrValue){
        Users user = (Users)request.getSession().getAttribute("LOGIN_DATA");  //获取当前登录对象
        List<DbConfig> listConfig = dbConfigService.findConfig(user.getId());  //查找当前用户的所有配置
        int status = 0;
        for (int i = 0; i < listConfig.size(); i++) {
            if(dbName.equals(listConfig.get(i).getDbname())){    //遍历所有配置，找到需要的配置
                status= dbExecuteService.updateRowData(listConfig.get(i),tbName,arrId,arrValue);    //查询表内容
                break;
            }
        }
       /*for(int i = 0 ;i <arrId.length;i++){
            System.out.println(arrId[i]+":"+arrValue[i]);
        }
*/
        return status;
    }


    //数据库配置页面
    @RequestMapping("/dbConfigAdmin")
    public String dbConfigAdmin(HttpServletRequest request){
        Users user = (Users)request.getSession().getAttribute("LOGIN_DATA");  //获取当前登录对象

        List<SqlDescComm>  tbColumn = dbConfigService.queryTbColumnName("dbconfig");
        List<DbConfig> listConfig = new ArrayList<>();

        listConfig.add( fieldObject(tbColumn) );
        listConfig.addAll( dbConfigService.findConfig(user.getId()) );  //查找当前用户的所有配置
        request.getSession().setAttribute("userDbConfig",listConfig);
        return "dbConfigAdmin";
    }

    //修改数据库配置
    @RequestMapping("/upDbConfig")
    public @ResponseBody boolean upDbConfig(HttpServletRequest request,String[] arrId,String[] arrValue){
        DbConfig dbConfig = fieldObjectData(arrId,arrValue);
        dbConfigService.updateConfig(dbConfig);
        return true;
    }

    //测试当前数据库配置是否正确
    @RequestMapping("/testConfig")
    public @ResponseBody boolean testConfig(String[] arrId,String[] arrValue){
        DbConfig dbConfig = new DbConfig();
        Field[] field = dbConfig.getClass().getDeclaredFields();  //获取实体类的所有属性，返回Field数组
        for (int i = 0; i < arrId.length; i++) {  //遍历所有属性
            String name = arrId[i];    //获取属性的名字
            name = "set" +name.substring(0, 1).toUpperCase() + name.substring(1); //将属性的首字符大写，构造set方法
            Method m = null;
            try {
                m = dbConfig.getClass().getMethod( name,String.class);
                m.invoke(dbConfig,arrValue[i]);    //调用setter方法获取属性值
            } catch (NoSuchMethodException e) {
                e.printStackTrace();
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            } catch (InvocationTargetException e) {
                e.printStackTrace();
            }
        }
        boolean dbconfigLink = dbExecuteService.dbconfigTest(dbConfig);

        return dbconfigLink;

    }

    //添加数据库配置
    @RequestMapping("/addDbConfig")
    public @ResponseBody Integer addDbConfig(String[] arrId,String[] arrValue){
        DbConfig dbConfig = fieldObjectData(arrId,arrValue);
        dbConfig.setDbid(  UUID.randomUUID().toString().replaceAll("-",""));
        int status = dbConfigService.addConfig(dbConfig);
        return status;
    }


    //构造修改配置页面
    @RequestMapping("/upDbConfigPage")
    public String upDbConfigPage(HttpServletRequest request,String dbid){

        DbConfig dbConfig =  dbConfigService.findConfigOnDbid(dbid);
        request.getSession().setAttribute("upDbConfig",dbConfig);
        return "upDbConfig";

    }

    //反射Dbconfig 返回数据对象
    public DbConfig fieldObjectData(String[] arrId,String[] arrValue){

        DbConfig dbConfig = new DbConfig();
        Field[] field = dbConfig.getClass().getDeclaredFields();  //获取实体类的所有属性，返回Field数组
        for (int i = 0; i < arrId.length; i++) {  //遍历所有属性
            String name = arrId[i];    //获取属性的名字
            name = "set" +name.substring(0, 1).toUpperCase() + name.substring(1); //将属性的首字符大写，构造set方法
            Method m = null;
            try {
                m = dbConfig.getClass().getMethod( name,String.class);
                m.invoke(dbConfig,arrValue[i]);    //调用setter方法获取属性值
            } catch (NoSuchMethodException e) {
                e.printStackTrace();
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            } catch (InvocationTargetException e) {
                e.printStackTrace();
            }
        }
        return dbConfig;
    }

    //反射DbConfig   返回列名
    public DbConfig fieldObject(List<SqlDescComm>  tbColumn){

        int tbColumnStart=  0 ;  //表示当前第x个属性
        DbConfig dbConfig = new DbConfig();
        Field[] field = dbConfig.getClass().getDeclaredFields();  //获取实体类的所有属性，返回Field数组
        for (int j = 0; j < field.length; j++) {  //遍历所有属性
            String name = field[j].getName();    //获取属性的名字
            name = name.substring(0, 1).toUpperCase() + name.substring(1); //将属性的首字符大写，方便构造get，set方法

            Method m = null;
            try {
                m = dbConfig.getClass().getMethod("set" + name,String.class);
                m.invoke(dbConfig,tbColumn.get(tbColumnStart).getField());    //调用setter方法获取属性值
                tbColumnStart++;
            } catch (NoSuchMethodException e) {
                e.printStackTrace();
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            } catch (InvocationTargetException e) {
                e.printStackTrace();
            }
        }
        return dbConfig;
    }


    //构造添加配置页面
    @RequestMapping("/addDbConfigPage")
    public String addDbConfigPage(){
        return "addDbConfig";
    }

    //构造sqlplus 页面
    @RequestMapping("/sqlplus")
    public String sqlplus(){
        return "sqlplus";
    }


    //执行sqlplus传过来的命令
    @RequestMapping("/runSql")
    public  @ResponseBody List<List> runSql (HttpServletRequest request,String dbName,String sql){
        Users user = (Users)request.getSession().getAttribute("LOGIN_DATA");  //获取当前登录对象
        List<DbConfig> listConfig = dbConfigService.findConfig(user.getId());  //查找当前用户的所有配置
        List<List> retuList = new ArrayList<>();
        for (int i = 0; i < listConfig.size(); i++) {
            if(dbName.equals(listConfig.get(i).getDbname())){    //遍历所有配置，找到需要的配置
                retuList= dbExecuteService.runSql(listConfig.get(i),sql);    //查询表内容
                break;
            }
        }

        return retuList;
    }

}
